﻿--（1）编写一个函数求该银行的金额总和
use BankTest
go
create function func_GetBankMoneyByAccId()
returns money
as
begin
	declare @SumBankMoney money
	select @SumBankMoney =(select sum(CardMoney ) from BankCard)	
	return @SumBankMoney 
end
go


select dbo.func_GetBankMoneyByAccId() as 银行金额总和


--（2）传入账户编号，返回账户真实姓名
go
create function func_GetInfoByAcco_Id(@id int)
returns varchar(40)
as
begin
	declare @name varchar(40)
	select @name = (select RealName from AccountInfo where AccountId = @id)
	return @name
end
go

select dbo.func_GetInfoByAcco_Id( 2 ) as 姓名


--（3）传递开始时间和结束时间，返回交易记录（存钱取钱），交易记录中包含 真实姓名，卡号，存钱金额，取钱金额，交易时间。

--方案一（逻辑复杂，函数内容除了返回结果的sql语句还有其他内容，例如定义变量等）：

go
create function func_GetCardExchangeByOpenTimeAndEndTime(@OpenTime smalldatetime,@EndTime smalldatetime)
returns @ExchangTable table(
	Name varchar(30),
	CardNo varchar(50),
	MoneyInBank money,
	MoneyOutBank money,
	ExchangeTime smalldatetime
)
as
begin
	insert into @ExchangTable
	select A.RealName,CE.CardNo,CE.MoneyInBank,CE.MoneyOutBank,CE.ExchangeTime from CardExchange CE 
	join BankCard B on B.CardNo = CE.CardNo
	join AccountInfo A on A.AccountId = B.AccountId
	where A.OpenTime = @OpenTime and CE.ExchangeTime = @EndTime
	return
end
go

select * from func_GetCardExchangeByOpenTimeAndEndTime('2022-09-19 22:37:00','2022-09-19 22:37:00')




--（4）查询银行卡信息，将银行卡状态1,2,3,4分别转换为汉字“正常,挂失,冻结,注销”，根据银行卡余额显示银行卡等级 30万以下为
--“普通用户”,30万及以上为"VIP用户"，分别显示卡号，身份证，姓名，余额，用户等级，银行卡状态。

--方案一：直接在sql语句中使用case when
select B.CardNo,AccountCode,RealName,CardMoney,
(
	case when CardMoney<300000 then '普通用户'
	else 'VIP用户'
	end
)用户等级,
(
	case when CardState=1 then '正常'
		 when CardState=2 then '挂失'
		 when CardState=3 then '冻结' else '注销'
	end
)银行卡状态
from BankCard B
join AccountInfo A on A.AccountId = B.AccountId


--方案二：将等级和状态用函数实现
--查询银行卡信息，将银行卡状态1,2,3,4分别转换为汉字“正常,挂失,冻结,注销”，根据银行卡余额显示银行卡等级 30万以下为
--“普通用户”,30万及以上为"VIP用户"，分别显示卡号，身份证，姓名，余额，用户等级，银行卡状态。
go
create function fun_GetBankInfoByCardNo(@CardNo varchar(30))
returns @BankInfo table(
	CardNo varchar(30),
	AccountCode varchar(30),
	Name varchar(30),
	CardMoney money,
	UserLevel varchar(30),
	BankCardStatus varchar(30)
)
as
begin
	insert into @BankInfo
	select B.CardNo,AccountCode,RealName,CardMoney,
	(
		case when CardMoney<300000 then '普通用户'
		else 'VIP用户'
		end
	)UserLevel,
	(
		case when CardState=1 then '正常'
			 when CardState=2 then '挂失'
			 when CardState=3 then '冻结' else '注销'
		end
	)BankCardStatus
	from BankCard B
	join AccountInfo A on A.AccountId = B.AccountId
	where CardNo = @CardNo
	return
end
go

select * from fun_GetBankInfoByCardNo('6225547854125656')


select * from AccountInfo
select * from CardExchange
select * from BankCard


--（5）表格。。。。。。。。。。。
create table Emp
(
	EmpId int primary key identity(1,2), --自动编号
	empName varchar(20), --姓名
	empSex varchar(4),   --性别
	empBirth smalldatetime --生日
)
insert into Emp(empName,empSex,empBirth) values('刘备','男','2008-5-8')
insert into Emp(empName,empSex,empBirth) values('关羽','男','1998-10-10')
insert into Emp(empName,empSex,empBirth) values('张飞','男','1999-7-5')
insert into Emp(empName,empSex,empBirth) values('赵云','男','2003-12-12')
insert into Emp(empName,empSex,empBirth) values('马超','男','2003-1-5')
insert into Emp(empName,empSex,empBirth) values('黄忠','男','1988-8-4')
insert into Emp(empName,empSex,empBirth) values('魏延','男','1998-5-2')
insert into Emp(empName,empSex,empBirth) values('简雍','男','1992-2-20')
insert into Emp(empName,empSex,empBirth) values('诸葛亮','男','1993-3-1')
insert into Emp(empName,empSex,empBirth) values('徐庶','男','1994-8-5')

--编写函数，根据出生日期求年龄，年龄求实岁，例如：

--​		生日为2000-5-5，当前为2018-5-4，年龄为17岁
--​		生日为2000-5-5，当前为2018-5-6，年龄为18岁

select * from Emp
go
create function fun_GetAgeByBirth(@id int,@NowTime datetime)
returns int
as
begin
	declare @BirthTime datetime
	declare @age int
	select @BirthTime = (select empBirth from Emp where EmpId = @id)
	select @age = DATEDIFF(YY,@BirthTime,@NowTime)
	select @age = case when DATEDIFF(DD,DATEADD(YY,@age,@BirthTime),@NowTime)<0 then @age-1 else @age end
	return @age
end
go

select dbo.fun_GetAgeByBirth(9,GETDATE()) as 年龄
